﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CarLoanSystem.Entity;
using System.Data.SqlClient;
using System.Data;

namespace CarLoanSystem.DAL
{
    public class InterrestInfoDAL : DBBase
    {
        public static InterrestInfoItem LoadInterrest(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT TOP 1000 [INTERREST_ID]
                                                      ,[INTERREST_RATE]
                                                      ,[DEBT_MONTH]
                                                      ,[DOWN_PAYMENT_ID]
                                                    FROM [CAR_LOAN].[dbo].[INTERREST_INFO] 
                                                    where [INTERREST_ID] = @INTERREST_ID ", DBBase.con);
                command.Parameters.AddWithValue("@INTERREST_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    InterrestInfoItem item = new InterrestInfoItem();
                    item.INTERREST_ID = Convert.ToInt32(dt.Rows[0]["INTERREST_ID"].ToString());
                    item.INTERREST_RATE = Convert.ToDecimal(dt.Rows[0]["INTERREST_RATE"].ToString());
                    item.DOWN_PAYMENT_ID = Convert.ToInt32(dt.Rows[0]["DOWN_PAYMENT_ID"].ToString());
                    item.DEBT_MONTH = Convert.ToInt32(dt.Rows[0]["DEBT_MONTH"].ToString());
                    return item;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadAllInterrest(string id, string InterrestRate)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                string query = @"SELECT [INTERREST_ID]
                                  ,[INTERREST_RATE]
                                  ,[DEBT_MONTH]
                                  ,[DOWN_PAYMENT_ID]
                              FROM [CAR_LOAN].[dbo].[INTERREST_INFO] where 1=1";
                if (id != "")
                {
                    query += " And INTERREST_ID = " + id;
                }
                if (InterrestRate != "")
                {
                    query += " And INTERREST_RATE = " + InterrestRate;
                }

                SqlCommand command = new SqlCommand(query, DBBase.con);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static bool Insert(InterrestInfoItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"INSERT INTO [CAR_LOAN].[dbo].[INTERREST_INFO]
                                                       ([INTERREST_RATE] , [DEBT_MONTH] , [DOWN_PAYMENT_ID])
                                                 VALUES
                                                      ( @INTERREST_RATE , @DEBT_MONTH , @DOWN_PAYMENT_ID)", DBBase.con);
                command.Parameters.AddWithValue("@INTERREST_RATE", item.INTERREST_RATE);
                command.Parameters.AddWithValue("@DEBT_MONTH", item.DEBT_MONTH);
                command.Parameters.AddWithValue("@DOWN_PAYMENT_ID", item.DOWN_PAYMENT_ID);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
                return true;
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static bool Update(InterrestInfoItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[INTERREST_INFO]
                                                    SET [INTERREST_RATE] = @INTERREST_RATE,
                                                        [DEBT_MONTH] = @DEBT_MONTH,
                                                        [DOWN_PAYMENT_ID] = @DOWN_PAYMENT_ID
                                             WHERE ([INTERREST_ID] = @INTERREST_ID)", DBBase.con);
                command.Parameters.AddWithValue("@INTERREST_RATE", item.INTERREST_RATE);
                command.Parameters.AddWithValue("@DEBT_MONTH", item.DEBT_MONTH);
                command.Parameters.AddWithValue("@DOWN_PAYMENT_ID", item.DOWN_PAYMENT_ID);
                command.Parameters.AddWithValue("@INTERREST_ID", item.INTERREST_ID);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
                return true;
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }
    }
}
